# -*- coding: utf-8 -*-
"""
@Time ： 2021/11/30 11:52
@Auth ： 颜
@File ：top250.py
@IDE ：PyCharm
"""
# import urllib
import re

import requests
import time
from bs4 import BeautifulSoup
import xlwt
import sqlite3


#


class DoubanTop250(object):
    def __init__(self):
        self.datapath = 'douban.db'
        self.url = 'https://movie.douban.com/top250?start={}&filter='
        self.headers = {
            "User-Agent": 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/93.0.4577.82 Safari/537.36'}

    def get_url(self, url):

        response = requests.get(url=url, headers=self.headers)
        html_response = response.content.decode()
        # html_response = html_content
        return html_response

    def parse(self):
        data_list = []
        for i in range(0, 10):
            html_response = self.get_url(self.url.format(i * 25))
            bs = BeautifulSoup(html_response, 'html.parser')

            for item in bs.find_all('div', class_='item'):
                list_douban = []
                item = str(item)
                print(item)
                href = re.findall(r'.*<a href="(.*?)">', item)[0]
                list_douban.append(href)
                findTitle = re.compile(r'.*<span class="title">(.*?)</span>')
                title = re.findall(findTitle, item)[0]
                list_douban.append(title)
                other = re.findall(r'.*<span class="other">(.*?)</span>', item)[0]
                list_douban.append(other)
                findAuthor = re.compile(r'.*<p class="">(.*?)</p>', re.S)
                authors = re.findall(findAuthor, item)
                author = \
                    [a.replace(" ", "").replace("\n", "").replace("\xa0", "").replace("...<br/>", "") for a in authors][
                        0]
                list_douban.append(author)
                # print(author[0])
                # author = [a. for a in author]
                star = re.findall(r'<span class="rating_num".*>(.*?)</span>', item)[0]
                list_douban.append(star)
                comimt = re.findall(r'<span>(\d*人评价)</span>', item)[0]
                list_douban.append(comimt)
                print(comimt)
                quote = re.findall(r'<span class="inq">(.*?)</span>', item)
                quote = quote[0] if len(quote) > 0 else ''
                list_douban.append(quote)
                # if len(quote) > 0:
                #     list_douban.append(quote)
                # else:
                #     return None
                # quote = list_douban.append(quote) if len(quote) > 0 else None
                # print(quote)

                img = re.findall(r'<img .*src="(.*?)".*>', item)[0]
                list_douban.append(img)
                # datalist.append(list_douban)
                # print(datalist)
                data_list.append(list_douban)
        # print(data_list)
        return data_list

    def save_data(self, data_list, savepath):
        #     创建对象
        workbook = xlwt.Workbook(encoding='utf8', style_compression=0)
        worksheet = workbook.add_sheet('豆瓣电影', cell_overwrite_ok=True)
        col = ("详情页链接", '电影名', '别名', '电影导演和主演', '评分', '评价人数', '介绍', '图片链接')
        for i in range(0, 8):
            worksheet.write(0, i, col[i])
        for i in range(0, len(data_list)):
            data = data_list[i]
            print("开始爬取第{}条".format(i + 1))
            for j in range(0, 8):
                worksheet.write(i + 1, j, data[j])
        workbook.save(savepath)

    # 保存数据
    def save_data2(self, datapath, datalist):
        # self.create_sqllite(datapath)
        conn = sqlite3.connect(datapath)
        cur = conn.cursor()
        for data in datalist:
            for index in range(len(data)):
                data[index] = '"' + data[index] + '"'
            sql = '''
                insert into DOUBAN_DEMO (
                info_link,move_name,other_name,instroduction,socre,content_num,content,pic_link)
                values(%s)''' % ",".join(data)
            print(sql)
            cur.execute(sql)
            conn.commit()

        cur.close()
        conn.close()

    # 创建数据库
    def create_sqllite(self, datapath):
        connt = sqlite3.connect(datapath)
        # 创建表
        c = connt.cursor()
        c.execute('''
                    CREATE TABLE DOUBAN_DEMO (ID integer primary key autoincrement, 
                    info_link text,
                    move_name varchar,
                    other_name varchar,
                    instroduction text,
                    socre varchar,
                    content_num varchar ,
                    content text,
                    pic_link text
                    )
            ''')
        print('创建表成功')
        connt.commit()
        connt.close()

    def run(self):
        self.get_url(self.url)
        data_list = self.parse()
        savepath = '豆瓣top250.xls'
        self.save_data(data_list, savepath)
        self.create_sqllite(self.datapath)
        self.save_data2(datalist=data_list, datapath=self.datapath)


if __name__ == '__main__':
    d = DoubanTop250()
    d.run()
